package com.hex.ds.em.hcdp.impala.service;

import cn.hutool.core.util.StrUtil;
import com.hex.ds.em.hcdp.common.entity.ScriptPackScanResultEntity;
import com.hex.ds.em.hcdp.common.enums.AntlrEnum;
import com.hex.ds.em.hcdp.common.utils.SqlFormatterUtil;
import com.hex.ds.em.hcdp.impala.gen.ImpalaLexer;
import com.hex.ds.em.hcdp.impala.gen.ImpalaParser;
import com.hex.ds.em.hcdp.impala.gen.ImpalaParserBaseVisitor;
import com.hex.hdtp.common.utils.AntlrUtil;
import lombok.extern.slf4j.Slf4j;
import org.antlr.v4.runtime.CharStreams;
import org.antlr.v4.runtime.CodePointCharStream;
import org.antlr.v4.runtime.CommonTokenStream;
import org.antlr.v4.runtime.tree.ErrorNode;
import org.antlr.v4.runtime.tree.TerminalNode;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

@Slf4j
@Component
public class ImpalaBaseSqlVisitor extends ImpalaParserBaseVisitor<String> {

    public static ImpalaBaseSqlVisitor impalaBaseSqlVisitor;

    @PostConstruct
    public void init() {
        impalaBaseSqlVisitor = this;
    }

    public static final String MARK_BEGIN = "/*";
    public static final String MARK_END = "*/";
    public static final String MARK_CODE = "[CODE]";
    public static final String ERROR_CODE = "[ERROR]";
    public static final String WARN_CODE = "[WARN]";
    public static final String ERROR_MARK_BEGIN = StrUtil.format(" {} {} {}", MARK_BEGIN, MARK_CODE, ERROR_CODE);
    public static final String WARN_MARK_BEGIN = StrUtil.format(" {} {} {}", MARK_BEGIN, MARK_CODE, WARN_CODE);

    // 语法解析异常标识 (遇到一次解析异常之后则打标记)
    private boolean isAnalyticException = false;

    //临时表是否有分区字段
    private boolean partition = false;

    //sql是否使用临时表
    public boolean tem = false;

    //文件是否有临时表操作
    public boolean temFlag = false;

    //sql是否有更新数据操作
    public boolean updateData = false;

    //脚本是否更新数据操作标识
    public boolean isUpdateData = false;

    //insert的表名
    public static List<String> insertTableName = new ArrayList<>();

    public boolean getIsAnalyticException() {
        return isAnalyticException;
    }

    // 扫描出的库表名信息
    private List<String> databaseNameList = new ArrayList<>();

    public List<String> getDatabaseNameList() {
        return databaseNameList;
    }

    // 扫描结果清单Map
    private Map<String, ScriptPackScanResultEntity> scriptPackScanResultMaps = new HashMap<>();

    // 存在时间分区字段的表
    private List<String> timePartitionTableList = new ArrayList<>();

    // 动态配置文件信息
    private Map<String, String> paramsConfig = new HashMap<>();

    // 文件名
    private String fileName = "";

    //是否脚本最后一段sql
    private boolean endFlag;

    //left join的表
    private String leftJoinTableName;

    //join语句是否是left join
    boolean leftJoin = false;

    // 扫描结果清单
    public List<ScriptPackScanResultEntity> getScriptPackScanResultList() {
        return new ArrayList<>(scriptPackScanResultMaps.values());
    }

    /*public String visitRoot(final String sourceSql, final Map<String, ScriptPackScanResultEntity> scriptPackScanResults, List<String> timePartitionTableList,  String fileName) {
        this.scriptPackScanResultMaps = scriptPackScanResults;
        this.timePartitionTableList = timePartitionTableList;
        this.fileName = fileName;
        final CodePointCharStream input = CharStreams.fromString(sourceSql);
        ImpalaLexer lexer = new ImpalaLexer(input);
        CommonTokenStream token = new CommonTokenStream(lexer);
        ImpalaParser parser = new ImpalaParser(token);
        String resultStr = this.visit(parser.statements());
        return SqlFormatterUtil.formatterSqlWithSpace(resultStr);
    }*/

    public String visitRoot(final String sourceSql, final Map<String, ScriptPackScanResultEntity> scriptPackScanResults, String fileName, boolean endFlag, boolean temFlag, boolean updateDataFlag) {
        this.scriptPackScanResultMaps = scriptPackScanResults;
        this.fileName = fileName;
        this.endFlag = endFlag;
        this.temFlag = temFlag;
        this.isUpdateData = updateDataFlag;
        final CodePointCharStream input = CharStreams.fromString(sourceSql);
        ImpalaLexer lexer = new ImpalaLexer(input);
        CommonTokenStream token = new CommonTokenStream(lexer);
        ImpalaParser parser = new ImpalaParser(token);
        String resultStr = this.visit(parser.statements());
        return SqlFormatterUtil.formatterSqlWithSpace(resultStr);
    }

    /*public String visitRoot(final String sourceSql) {
        final CodePointCharStream input = CharStreams.fromString(sourceSql);
        ImpalaLexer lexer = new ImpalaLexer(input);
        CommonTokenStream token = new CommonTokenStream(lexer);
        ImpalaParser parser = new ImpalaParser(token);
        return this.visit(parser.statements());
    }*/

    @Override
    public String visitCreateTableStatement(ImpalaParser.CreateTableStatementContext ctx) {
        String resultStr = super.visitCreateTableStatement(ctx);
        try {
            String tableName = ctx.tableName(0).getText();
            // 是否禁止出现CREATE检查
            resultStr = scanCreate(tableName, resultStr);
            // 检查建表名是否符合规范
            resultStr = scanTableName(tableName, resultStr);
            // 检查表是否已在元数据库内
            /*resultStr = scanTableExist(tableName, "CREATE", resultStr);*/
            // 检查库名是否参数化
            resultStr = scanDbName(tableName, "CREATE", resultStr);

            if (StrUtil.containsAnyIgnoreCase(tableName,"_tmp")){
                tem = true;
                //检查临时表(表名含tmp)是否包含分区字段
                resultStr = scanPartitionByTmpTable(resultStr);
            }
        } catch (Exception e) {
            log.error("【antlr处理异常】visitCreateTableStatement出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitCreateTableStatement出现异常" + e.getMessage(), e);
        }
        return resultStr;
    }

    @Override
    public String visitStatement(ImpalaParser.StatementContext ctx) {
        String resultStr = super.visitStatement(ctx);
        try {
            String sql = ctx.getText().toUpperCase();
            //判断sql是否修改数据
            if (StrUtil.containsAnyIgnoreCase(sql,"UPDATE","DELETE","INSERT")&&scriptPackScanResultMaps.containsKey(AntlrEnum.END_METADATA_REFRESH_CHECK.getValue())&&scriptPackScanResultMaps.containsKey(AntlrEnum.END_STATISTICAL_TABLE_CHECK.getValue())){
                updateData = true;
            }
            if (endFlag) {
                if (isUpdateData || StrUtil.containsAnyIgnoreCase(sql,"UPDATE","DELETE","INSERT")){
                    //检查程序尾部是否包含分析表操作
                    resultStr = scanAnalyze(resultStr, sql);
                    //检查尾部是否刷新元数据
                    resultStr = scanMetadataRefresh(resultStr, sql);
                }
                if (temFlag || StrUtil.containsAnyIgnoreCase(sql, "_tmp")) {
                    //检查程序尾部是否显式删除临时表
                    resultStr = scanTemDrop(resultStr, sql);
                }
            }
            //检查left join是否必要
            resultStr = scanLeftJoin(resultStr, sql);
            //检查作业是否只操作单个分区
            resultStr = scanPartitionNum(resultStr,sql);
            //分区表，分区字段不使用
            resultStr = scanPartitionTable(resultStr,sql);
            //Union ALL检查
            resultStr = scanUnionAll(resultStr,sql);
        } catch (Exception e) {
            log.error("【antlr处理异常】visitStatement出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitStatement出现异常" + e.getMessage(), e);
        }
        return resultStr;
    }

    private String scanUnionAll(String resultStr, String sql) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.UNION_ALL_CHECK.getValue())&&sql.contains("UNIONALL")) {
            boolean isMark = sql.contains("MAX_TABLE_");
            setScriptPackScanResults(AntlrEnum.UNION_ALL_CHECK.getValue(), "", "", isMark, "necessary");
            return !isMark ? getMarkResult(AntlrEnum.UNION_ALL_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    private String scanPartitionTable(String resultStr, String sql) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.PARTITION_TABLE_CHECK.getValue())) {
            String upperCase = sql.toUpperCase();
            boolean isMark = checkPartitionTable(upperCase);
            setScriptPackScanResults(AntlrEnum.PARTITION_TABLE_CHECK.getValue(), "", "", isMark, "necessary");
            return !isMark ? getMarkResult(AntlrEnum.PARTITION_TABLE_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    private boolean checkPartitionTable(String sql) {
        if(sql.contains("PARTITION_TABLE_")){
            return sql.contains("_PARTITION");
        }
        return true;
    }

    private String scanPartitionNum(String resultStr, String sql) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.SQL_PARTITION_NUM_CHECK.getValue())&&StrUtil.containsAnyIgnoreCase(sql,"leftjoin")) {
            String upperCase = sql.toUpperCase();
            boolean isMark = checkPartitionNum(upperCase);
            setScriptPackScanResults(AntlrEnum.SQL_PARTITION_NUM_CHECK.getValue(), "", "", isMark, "necessary");
            return !isMark ? getMarkResult(AntlrEnum.SQL_PARTITION_NUM_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    private boolean checkPartitionNum(String sql) {
        // 定义一个计数器来计算"_PARTITION"的出现次数
        int count = 0;
        // 获取"_PARTITION"的长度，用于后续的比较
        int partitionLength = "_PARTITION".length();

        // 遍历字符串，检查"_PARTITION"出现的次数
        for (int i = 0; i <= sql.length() - partitionLength; i++) {
            // 使用substring方法截取与"_PARTITION"等长的子字符串进行比较
            if (sql.substring(i, i + partitionLength).equals("_PARTITION")) {
                count++;
                // 如果计数已经超过2次，直接返回false
                if (count > 2) {
                    return false;
                }
            }
        }
        // 如果计数没有超过2次，返回实际计数（或者可以根据需求返回其他内容）
        return true;
    }

    private String scanTemDrop(String resultStr, String sql) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.TEM_DROP_CHECK.getValue())) {
            boolean isMark = StrUtil.containsAnyIgnoreCase(sql, "DROPTABLE", "TRUNCATETABLE");
            setScriptPackScanResults(AntlrEnum.TEM_DROP_CHECK.getValue(), "", "", isMark, "necessary");
            return !isMark ? getMarkResult(AntlrEnum.TEM_DROP_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    @Override
    public String visitSearchCondition(ImpalaParser.SearchConditionContext ctx) {
        String resultStr = super.visitSearchCondition(ctx);
        String sql = ctx.getText().toUpperCase();
        try {
            //检查or条件是否可以用in替换
            resultStr = scanOrCondition(resultStr, sql);
        } catch (Exception e) {
            log.error("【antlr处理异常】visitSearchCondition出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitSearchCondition出现异常" + e.getMessage(), e);
        }
        return resultStr;
    }


    private String scanOrCondition(String resultStr, String sql) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.OR_CONDITION_CHECK.getValue())) {
            boolean isMark = canReplaceOrWithIn(sql);
            setScriptPackScanResults(AntlrEnum.OR_CONDITION_CHECK.getValue(), "", "", !isMark, "necessary");
            return isMark ? getMarkResult(AntlrEnum.OR_CONDITION_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    /**
     * 检查查询条件中or是否可以替换为in
     *
     * @param sql
     * @return
     */
    private boolean canReplaceOrWithIn(String sql) {
        // 正则表达式，考虑等号周围和OR周围可能有空格，且值可能被单引号或双引号包围
        String OR_CONDITION_PATTERN =
                "(\\bDEPARTMENT\\b)\\s*=\\s*(?:'([^']*)'|\"([^\"]*)\"|([^\\sOR]*))(?:\\s*OR\\s*\\1\\s*=\\s*(?:'([^']*)'|\"([^\"]*)\"|([^\\sOR]*)))+";
        String VALUE_PATTERN =
                "(?:'([^']*)'|\"([^\"]*)\"|([^\\sOR]+))";

        // 编译正则表达式
        Pattern pattern = Pattern.compile(OR_CONDITION_PATTERN, Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        Matcher matcher = pattern.matcher(sql);

        // 检查是否有匹配的OR条件
        if (!matcher.find()) {
            return false;
        }

        // 提取列名和所有值
        Set<String> valuesSet = new HashSet<>();

        // 使用另一个匹配器来提取所有值，确保没有重复
        Pattern valuePattern = Pattern.compile(VALUE_PATTERN);
        Matcher valueMatcher = valuePattern.matcher(matcher.group(0));
        while (valueMatcher.find()) {
            // 根据捕获组提取值
            if (valueMatcher.group(1) != null) {
                valuesSet.add(valueMatcher.group(1));
            } else if (valueMatcher.group(2) != null) {
                valuesSet.add(valueMatcher.group(2));
            } else {
                valuesSet.add(valueMatcher.group(3));
            }
        }

        // 如果所有值都是唯一的，并且只涉及一个列名，则可以替换为IN
        return valuesSet.size() > 1; // 至少有两个不同的值才考虑替换为IN
    }

    private String scanMetadataRefresh(String resultStr, String sql) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.END_METADATA_REFRESH_CHECK.getValue())) {
            boolean isMark = sql.contains("REFRESH") || sql.contains("INVALIDATE METADATA");
            setScriptPackScanResults(AntlrEnum.END_METADATA_REFRESH_CHECK.getValue(), "", "", isMark, "necessary");
            return !isMark ? getMarkResult(AntlrEnum.END_METADATA_REFRESH_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    @Override
    public String visitJoinSourcePart(ImpalaParser.JoinSourcePartContext ctx) {
        String resultStr = super.visitJoinSourcePart(ctx);
        try {
            String tableName = ctx.getText();
            //left join表名
            leftJoinTableName = tableName.toUpperCase();

        } catch (Exception e) {
            log.error("【antlr处理异常】visitJoinSourcePart出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitJoinSourcePart出现异常" + e.getMessage(), e);
        }
        return resultStr;
    }

    @Override
    public String visitJoinSource(ImpalaParser.JoinSourceContext ctx) {
        String resultStr = super.visitJoinSource(ctx);
        try {
            String sql = ctx.getText().toUpperCase();
            //判断join是否是left join
            if (StrUtil.containsAnyIgnoreCase(sql, "LEFT", "JOIN")) {
                leftJoin = true;
            }
            //检查on条件左右两边是否同一张表
            resultStr = scanOnCondition(sql, resultStr);
            //检查on条件是否跟join表相关联
            resultStr = scanLeftJoinOnCondition(sql, resultStr,leftJoinTableName);
        } catch (Exception e) {
            log.error("【antlr处理异常】visitJoinSource出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitJoinSource出现异常" + e.getMessage(), e);
        }
        return resultStr;
    }

    private String scanLeftJoinOnCondition(String sql, String resultStr, String joinTableName) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.LEFT_JOIN_ON_CONDITION_CHECK.getValue())&&StrUtil.containsAnyIgnoreCase(sql,"LEFTJOIN")) {
            boolean isMark = checkOnClause(sql,joinTableName);
            if (!isMark){
                setScriptPackScanResults(AntlrEnum.LEFT_JOIN_ON_CONDITION_CHECK.getValue(), "", "", false, "necessary");
            }else {
                setScriptPackScanResults(AntlrEnum.LEFT_JOIN_ON_CONDITION_CHECK.getValue(), "", "", true, "necessary");
            }
            return !isMark ? getMarkResult(AntlrEnum.LEFT_JOIN_ON_CONDITION_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    private boolean checkOnClause(String sql, String joinTableName){
        // 正则表达式模式，用于匹配 ON 条件中的表名（忽略空格）
        // 这里使用了 \\s* 来匹配任意数量的空格（包括0个）
        // 注意：这种正则表达式可能不是严格的SQL解析器，它只是为了处理特定的格式问题
        String regex = "ON\\s*([a-zA-Z0-9_]+)\\s*\\.\\s*([a-zA-Z0-9_]+)\\s*=\\s*([a-zA-Z0-9_]+)\\s*\\.\\s*([a-zA-Z0-9_]+)";

        // 创建 Pattern 对象
        Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);

        // 创建 Matcher 对象
        Matcher matcher = pattern.matcher(sql);

        // 迭代查找匹配项
        while (matcher.find()) {
            // 提取左右两边的表名
            String leftTable = matcher.group(1);
            String rightTable = matcher.group(3);

            // 检查是否包含 joinTableName
            if (leftTable.equalsIgnoreCase(joinTableName) || rightTable.equalsIgnoreCase(joinTableName)) {
                return true;
            }
        }

        // 未找到匹配的表名
        return false;
    }

    private String scanOnCondition(String sql, String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.ON_CONDITION_CHECK.getValue())) {
            boolean isMark = checkIfSameTableInOnClause(sql);
            setScriptPackScanResults(AntlrEnum.ON_CONDITION_CHECK.getValue(), "", "", !isMark, "necessary");
            return isMark ? getMarkResult(AntlrEnum.ON_CONDITION_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    private boolean checkIfSameTableInOnClause(String sql) {
        // 定位ON关键字的位置
        int onIndex = sql.indexOf("ON");
        if (onIndex == -1) {
            return false; // 如果没有ON关键字，返回false
        }

        // 提取ON条件后的字符串
        String onClause = sql.substring(onIndex + 2).trim(); // 跳过ON和空格

        // 使用正则表达式匹配表名和列名（这里假设表名和列名都是由字母、数字和下划线组成的）
        Pattern pattern = Pattern.compile("([a-zA-Z_][a-zA-Z0-9_]*)\\.([a-zA-Z_][a-zA-Z0-9_]*)");
        Matcher matcher = pattern.matcher(onClause);

        // 存储出现的表名
        java.util.Set<String> tableNames = new java.util.HashSet<>();

        // 遍历匹配结果
        while (matcher.find()) {
            String tableName = matcher.group(1);
            tableNames.add(tableName);
        }

        // 如果表名集合中只有一个表名，说明ON条件后的左右两边涉及同一张表
        // 如果表名集合中有两个或更多不同的表名，说明ON条件后的左右两边涉及不同的表
        // 只需要检查是否有多于一个的表名
        return tableNames.size() != 2; // 期望ON条件后正好有两个不同的表名
    }

    @Override
    public String visitPrecedenceExpression(ImpalaParser.PrecedenceExpressionContext ctx) {
        String resultStr = super.visitPrecedenceExpression(ctx);
        try {
            String sql = ctx.getText();
            //检查是否存在Exists和In
            resultStr = scanExistsAndIn(sql, resultStr);
            //检查除法运算是否包含零值处理
            resultStr = scanDenominatorZero(sql,resultStr);
        } catch (Exception e) {
            log.error("【antlr处理异常】visitPrecedenceExpression出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitPrecedenceExpression出现异常" + e.getMessage(), e);
        }
        return resultStr;
    }

    private String scanDenominatorZero(String sql, String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.DENOMINATOR_ZERO_CHECK.getValue())) {
            String upperCase = sql.toUpperCase();
            boolean isMark = containsDivisionWithNullifCheck(upperCase);
            setScriptPackScanResults(AntlrEnum.DENOMINATOR_ZERO_CHECK.getValue(), "", "", isMark, "necessary");
            return !isMark ? getMarkResult(AntlrEnum.DENOMINATOR_ZERO_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    public static boolean containsDivisionWithNullifCheck(String input) {
        // 正则表达式匹配 "a/b" 格式
        String regex = "([^/]+)/([^/]+)";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(input);
        while (matcher.find()) {
            String denominator = matcher.group(2);  // 分母

            //判断是否有case零值处理
            if(StrUtil.containsAnyIgnoreCase(input,"CASE","WHEN"+denominator+"=0THEN0")){
                return true;
            }else if (denominator.contains("NULLIF")) {
                //检查分母是否包含 NULLIF 零值处理 NULLIF(value, 0)
                String nullifRegex = "NULLIF\\s*\\([^,)]+\\s*,\\s*0\\s*\\)";
                Pattern nullifPattern = Pattern.compile(nullifRegex);
                Matcher nullifMatcher = nullifPattern.matcher(denominator);

                if (nullifMatcher.find()) {
                    return true;  // 找到了符合条件的除法运算
                }
            }else {
                return false;//未作处理
            }
        }
        return true;  // 没有找到符合条件的除法运算
    }

    /**
     * 检查所有的exists和in
     *
     * @param sql
     * @return
     */
    private String scanExistsAndIn(String sql, String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.EXISTS_AND_IN_CHECK.getValue())) {
            String upperCase = sql.toUpperCase();
            boolean isMark = upperCase.contains("EXISTS") || upperCase.contains("IN");
            setScriptPackScanResults(AntlrEnum.EXISTS_AND_IN_CHECK.getValue(), "", "", !isMark, "necessary");
            return isMark ? getMarkResult(AntlrEnum.EXISTS_AND_IN_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    private String scanLeftJoin(String resultStr, String sql) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.LEFT_JOIN_CHECK.getValue())&&StrUtil.containsAnyIgnoreCase(sql,"leftjoin")) {
            String upperCase = sql.toUpperCase();
            boolean isMark = checksWhereClauseForLeftJoinTable(upperCase)&&StrUtil.containsAnyIgnoreCase(sql,"WHERE");
            setScriptPackScanResults(AntlrEnum.LEFT_JOIN_CHECK.getValue(), "", "", isMark, "necessary");
            return !isMark ? getMarkResult(AntlrEnum.LEFT_JOIN_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    private boolean checksWhereClauseForLeftJoinTable(String sql) {
        // 正则表达式匹配LEFT JOIN的表名，允许无空格情况
        Pattern joinPattern = Pattern.compile("LEFT\\s*JOIN\\s*(\\w+(?:\\.\\w+)?)", Pattern.CASE_INSENSITIVE);
        Matcher joinMatcher = joinPattern.matcher(sql);

        if (joinMatcher.find()) {
            String actualJoinTableName = joinMatcher.group(1);
            // 正则表达式匹配WHERE子句，并捕获其内容，允许无空格情况
            Pattern wherePattern = Pattern.compile("WHERE(.*?)(IS\\s+NOT?\\s+NULL|$)", Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
            Matcher whereMatcher = wherePattern.matcher(sql);

            if (whereMatcher.find()) {
                String whereClause = whereMatcher.group(1).trim();

                // 检查WHERE子句中是否包含JOIN的表名，允许无空格情况
                Pattern tableNamePattern = Pattern.compile("\\b" + actualJoinTableName + "\\b(?:\\.\\w+)?", Pattern.CASE_INSENSITIVE);
                Matcher tableNameMatcher = tableNamePattern.matcher(whereClause);

                if (tableNameMatcher.find()) {
                    // 进一步检查是否不是IS NULL或IS NOT NULL条件的一部分
                    Pattern isNullPattern = Pattern.compile("\\b" + actualJoinTableName + "\\b(?:\\.\\w+)\\s*(IS\\s+NOT?\\s+NULL)\\b", Pattern.CASE_INSENSITIVE);
                    Matcher isNullMatcher = isNullPattern.matcher(whereClause);

                    return !isNullMatcher.find(); // WHERE子句中包含JOIN的表名，且不是IS NULL/IS NOT NULL条件
                }
            }
        }
        return false; // WHERE子句中未包含JOIN的表名，或者是IS NULL/IS NOT NULL条件。
    }

    @Override
    public String visitWhereClause(ImpalaParser.WhereClauseContext ctx) {
        String resultStr = super.visitWhereClause(ctx);
        try {
            String whereStatement = ctx.getText();
            //检查where关联条件是否包含CASE WHEN、COALESCE、TRIM、日期函数
            resultStr = scanCorrelation(whereStatement, resultStr);
        } catch (Exception e) {
            log.error("【antlr处理异常】visitWhereClause出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitWhereClause出现异常" + e.getMessage(), e);
        }
        return resultStr;
    }

    @Override
    public String visitExpression(ImpalaParser.ExpressionContext ctx) {
        String resultStr = super.visitExpression(ctx);
        try {
            String expressionStatement = ctx.getText();
            //检查关联条件是否包含CASE WHEN、COALESCE、TRIM、日期函数
            resultStr = scanCorrelation(expressionStatement, resultStr);
            //检查关联条件on是否有1=1或其他明显错误
            resultStr = scanLeftJoinOnEqual(expressionStatement,resultStr);
        } catch (Exception e) {
            log.error("【antlr处理异常】visitExpression出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitExpression出现异常" + e.getMessage(), e);
        }
        return resultStr;
    }

    private String scanLeftJoinOnEqual(String sql, String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.LEFT_JOIN_ON_EQUAL_CHECK.getValue())) {
            String sqlUpperCase = sql.toUpperCase();
            boolean isMark = checkLeftJoinOn(sqlUpperCase);
            setScriptPackScanResults(AntlrEnum.LEFT_JOIN_ON_EQUAL_CHECK.getValue(), "", "", isMark, "necessary");
            return !isMark ? getMarkResult(AntlrEnum.LEFT_JOIN_ON_EQUAL_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    private boolean checkLeftJoinOn(String sql) {
        if (!sql.contains("=")){
            return true;
        }
        if (sql.contains("DT=1")){
            return false;
        }
        // 使用"="作为分隔符将sql字符串分割成两个部分
        String[] parts = sql.split("=", 2); // 第二个参数2确保只分割一次，即使sql中有多个"="

        // 检查分割后的数组长度是否为2（确保只有一个"="）
        if (parts.length != 2) {
            return true;
        }
        // 去除两部分字符串的前后空格
        String leftPart = parts[0].trim();
        String rightPart = parts[1].trim();
        // 比较两部分字符串是否相等
        return !leftPart.equals(rightPart);
    }

    /**
     * 禁止关联条件包含CASE WHEN、COALESCE、TRIM、日期函数
     *
     * @param condition
     * @param resultStr
     * @return
     */
    private String scanCorrelation(String condition, String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.CORRELATION_CONDITION_CHECK.getValue())) {
            String upperCondition = condition.toUpperCase();
            boolean isMark = upperCondition.contains("CASE") || upperCondition.contains("WHEN") || upperCondition.contains("TRIM") || upperCondition.contains("COALESCE") || containsFunction(condition);
            if (isMark) {
                setScriptPackScanResults(AntlrEnum.CORRELATION_CONDITION_CHECK.getValue(), "", "", false, "necessary");
            }
            return isMark ? getMarkResult(AntlrEnum.CORRELATION_CONDITION_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    /**
     * 判断是否包含Impala日期函数
     *
     * @param input
     * @return
     */
    public static boolean containsFunction(String input) {
        // 构建一个包含所有函数名称的正则表达式，使用\b来指定单词边界
        String regex = "\\b(date_add|date_format|date_sub|datediff|trunc|from_unixtime|current_date|LAST_DAY|to_date|unix_timestamp|weekofyear|add_months|current_timestamp|day|dayofmonth|extract|from_utc_timestamp|hour|last_day|minute|month|months_between|next_day|quarter|second|year)\\b";

        Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(input);

        // 如果找到匹配项，则返回true
        return matcher.find();
    }

    /**
     * 判断sql是否包含统计表操作
     *
     * @param resultStr
     * @param sql
     * @return
     */
    private String scanAnalyze(String resultStr, String sql) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.END_STATISTICAL_TABLE_CHECK.getValue())) {
            boolean isMark = sql.contains("ANALYZE TABLE") || sql.contains("ANALYZE");
            setScriptPackScanResults(AntlrEnum.END_STATISTICAL_TABLE_CHECK.getValue(), "", "", isMark, "necessary");
            return !isMark ? getMarkResult(AntlrEnum.END_STATISTICAL_TABLE_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    /**
     * 检查临时表（表名含tmp）是否包含分区字段
     *
     * @param resultStr
     * @return
     */
    private String scanPartitionByTmpTable(String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.TEM_TABLE_PARTITION_CHECK.getValue())) {
            boolean isMark;
            isMark = partition;
            tem = true;
            if (isMark) {
                setScriptPackScanResults(AntlrEnum.TEM_TABLE_PARTITION_CHECK.getValue(), "", "", false, "necessary");
            }
            return isMark ? getMarkResult(AntlrEnum.TEM_TABLE_PARTITION_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;

        }
        return resultStr;
    }

    @Override
    public String visitColumnNameTypeOrConstraintList(ImpalaParser.ColumnNameTypeOrConstraintListContext ctx) {
        String resultStr = super.visitColumnNameTypeOrConstraintList(ctx);
        String allColumnInfo = AntlrUtil.assembleChildren(ctx);
        // 检查必须字段
        resultStr = scanColumnNeed(allColumnInfo, resultStr);
        return resultStr;
    }

    @Override
    public String visitColumnNameTypeConstraint(ImpalaParser.ColumnNameTypeConstraintContext ctx) {
        String resultStr = super.visitColumnNameTypeConstraint(ctx);
        if (AntlrUtil.queryDesignateParent(ctx, ImpalaParser.CreateTableStatementContext.class)) {
            // 检验字段是否都有注释
            resultStr = scanColumnComment(resultStr, ctx);
            // 检验类型是否都为String与decimal两种
            resultStr = scanColumnType(resultStr, ctx);
        }
        return resultStr;
    }

    @Override
    public String visitAlterStatement(ImpalaParser.AlterStatementContext ctx) {
        String resultStr = super.visitAlterStatement(ctx);
        //TODO 功能与扫描做成多个visitor分离
        // 用于统计数据库名称
        databaseNameList.add(ctx.tableName().getText());
        // 检查库名参数化
        resultStr = scanDbName(ctx.tableName().getText(), "ALTER", resultStr);
        // 是否禁止出现ALTER检查
        resultStr = scanAlter(ctx.tableName().getText(), resultStr);
        return resultStr;
    }

    @Override
    public String visitWithClause(ImpalaParser.WithClauseContext ctx) {
        String resultStr = super.visitWithClause(ctx);
        resultStr = withAsScan(resultStr, ctx);
        return resultStr;
    }

    @Override
    public String visitCteStatement(ImpalaParser.CteStatementContext ctx) {
        ctx.identifier().getText();
        ctx.KW_AS();
        return super.visitCteStatement(ctx);
    }

    @Override
    public String visitSelectClause(ImpalaParser.SelectClauseContext ctx) {
        String resultStr = super.visitSelectClause(ctx);
        return resultStr;
    }

    @Override
    public String visitSelectList(ImpalaParser.SelectListContext ctx) {
        return super.visitSelectList(ctx);
    }

    /**
     * @param ctx
     * @Method visitSubQueryExpression
     * @Param
     * @Return java.lang.String
     * @Description 子查询检查
     * @Author Wang zhihao
     * @Date 2024/2/5 16:25
     * @Version V1.0
     */
    @Override
    public String visitSubQueryExpression(ImpalaParser.SubQueryExpressionContext ctx) {
        String resultStr = super.visitSubQueryExpression(ctx);
        // 子查询检查
        resultStr = subqueryScan(resultStr);
        return resultStr;
    }

    @Override
    public String visitSelectItem(ImpalaParser.SelectItemContext ctx) {
        String resultStr = super.visitSelectItem(ctx);
        try {
            // 检查字段血缘
            resultStr = scanColumnConsanguinity(resultStr, ctx);
        } catch (Exception e) {
            log.error("【antlr处理异常】visitAtomSelectStatement出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitSelectItem出现异常" + e.getMessage(), e);
        }
        return resultStr;
    }

    @Override
    public String visitFunction(ImpalaParser.FunctionContext ctx) {
        String resultStr = super.visitFunction(ctx);
        String functionInfo = ctx.getText();
        resultStr = sumFunctionScan(functionInfo, resultStr);
        return resultStr;
    }

    @Override
    public String visitErrorNode(ErrorNode node) {
        // 错误标记只打一次(因为语法错误会导致后续行多处解析异常)
        if (!isAnalyticException) {
            isAnalyticException = true;
            String resultStr = node.getText();
            return getMarkResult(AntlrEnum.ERROR_SQL.getDesc(), "necessary") + resultStr;
        } else {
            return node.getText();
        }
    }

    @Override
    public String visitStatements(ImpalaParser.StatementsContext ctx) {
        return super.visitStatements(ctx);
    }

    @Override
    public String visitCompute_stats_incremental_impala(ImpalaParser.Compute_stats_incremental_impalaContext ctx) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.COMPUTE_CHECK.getValue())) {
            setScriptPackScanResults(AntlrEnum.COMPUTE_CHECK.getValue(), "COMPUTE", "default", true, "warning");
        }
        return super.visitCompute_stats_incremental_impala(ctx);
    }

    @Override
    public String visitRefresh_impala(ImpalaParser.Refresh_impalaContext ctx) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.REFRESH_CHECK.getValue())) {
            setScriptPackScanResults(AntlrEnum.REFRESH_CHECK.getValue(), "REFRESH", "default", true, "necessary");
        }
        return super.visitRefresh_impala(ctx);
    }

    @Override
    public String visitInsertClause(ImpalaParser.InsertClauseContext ctx) {
        String resultStr = super.visitInsertClause(ctx);
        // 用于统计数据库名称
        String databaseName = ctx.destination() != null
                ? ctx.destination().tableOrPartition().tableName().getText()
                : ctx.tableOrPartition().tableName().getText();
        databaseNameList.add(databaseName);
        // 库名参数化校验
        resultStr = scanDbName(databaseName, "INSERT", resultStr);
        try {
            if (scriptPackScanResultMaps.containsKey(AntlrEnum.VALUE_DB_NAME.getValue())) {
                visitChildren(ctx);
                return ctx.tableOrPartition().getText() + "#" + ctx.columnNameList().getText();
            }
            // 插入语句时分区校验(无法在分区字节点处理-可能存在实际有分区，但是插入SQL中未设置)
            resultStr = scanPartitionByMetadata(resultStr, ctx);
            // INSERT OVERWRITE检查
            resultStr = scanInsertOverWrite(resultStr,ctx);
        } catch (Exception e) {
            log.error("【antlr处理异常】visitInsertClause出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitInsertClause出现异常" + e.getMessage(), e);
        }
        return resultStr;
    }

    private String scanInsertOverWrite(String resultStr, ImpalaParser.InsertClauseContext ctx) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.INSERT_OVER_WRITE_CHECK.getValue())) {
            String insertSql = ctx.getText().toUpperCase();
            String tableName = "";
            if (insertSql.contains("INSERTINTO")){
                tableName = ctx.tableOrPartition().tableName().getText();
            }
            if (insertSql.contains("INSERTOVERWRITE")){
                tableName = ctx.destination().tableOrPartition().tableName().getText();
            }
            boolean isMark = insertOverWriteCheck(insertSql,tableName);
            setScriptPackScanResults(AntlrEnum.INSERT_OVER_WRITE_CHECK.getValue(), "", "", isMark, "necessary");

            return !isMark ? getMarkResult(AntlrEnum.INSERT_OVER_WRITE_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    private boolean insertOverWriteCheck(String insertSql, String tableName) {
        if (insertTableName.contains(tableName)){
            return StrUtil.containsAnyIgnoreCase(insertSql, "INSERTINTO");
        }else{
            if (StrUtil.containsAnyIgnoreCase(insertSql,"INSERTOVERWRITE")){
                insertTableName.add(tableName);
                return true;
            }else {
                return false;
            }
        }

    }

    @Override
    public String visitValuesClause(ImpalaParser.ValuesClauseContext ctx) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.VALUE_DB_NAME.getValue())) {
            visitChildren(ctx);
            return ctx.getText();
        } else {
            return super.visitValuesClause(ctx);
        }
    }

    @Override
    public String visitRegularBody(ImpalaParser.RegularBodyContext ctx) {
        if (ctx.insertClause() != null && scriptPackScanResultMaps.containsKey(AntlrEnum.VALUE_DB_NAME.getValue())) {
            // 扫描检查insert-value语句
            String[] insertInfo = visitInsertClause(ctx.insertClause()).split("#");
            String valueInfo = visitValuesClause(ctx.valuesClause());
            scanInsert(insertInfo[0], insertInfo[1], valueInfo);
        }
        return super.visitRegularBody(ctx);
    }

    @Override
    public String visitCommitStatement(ImpalaParser.CommitStatementContext ctx) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.COMMIT_CHECK.getValue())) {
            setScriptPackScanResults(AntlrEnum.COMMIT_CHECK.getValue(), "COMMIT", "default", true, "necessary");
        }
        return super.visitCommitStatement(ctx);
    }

    @Override
    public String visitDeleteStatement(ImpalaParser.DeleteStatementContext ctx) {
        databaseNameList.add(ctx.tableName().getText());
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.DELETE_CHECK.getValue())) {
            setScriptPackScanResults(AntlrEnum.DELETE_CHECK.getValue(), "DELETE", ctx.tableName().getText(), false, "necessary");
        }
        return super.visitDeleteStatement(ctx);
    }

    @Override
    public String visitUpdateStatement(ImpalaParser.UpdateStatementContext ctx) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.VALUE_DB_NAME.getValue())) {
            scanUpdate(ctx.tableName().getText(), ctx.setColumnsClause().getText());
        }
        return super.visitUpdateStatement(ctx);
    }

    @Override
    public String visitDropTableStatement(ImpalaParser.DropTableStatementContext ctx) {
        String resultStr = super.visitDropTableStatement(ctx);
        databaseNameList.add(ctx.tableName().getText());
        resultStr = scanDbName(ctx.tableName().getText(), "DROP", resultStr);
        return resultStr;
    }

    @Override
    public String visitTruncateTableStatement(ImpalaParser.TruncateTableStatementContext ctx) {
        String resultStr = super.visitTruncateTableStatement(ctx);
        databaseNameList.add(ctx.tablePartitionPrefix().tableName().getText());
        return resultStr;
    }

    @Override
    public String visitTablePartition(ImpalaParser.TablePartitionContext ctx) {
        String resultStr = super.visitTablePartition(ctx);
        // 初始化脚本建表时判断是否存在指定分区字段
        resultStr = scanPartition(resultStr, ctx);
        //检查临时表是否有分区字段
        scanTmpPartition(ctx);
        return resultStr;
    }

    private void scanTmpPartition(ImpalaParser.TablePartitionContext ctx) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.TEM_TABLE_PARTITION_CHECK.getValue())) {
            String text = ctx.getText();
            String upperCase = text.toUpperCase();
            if (StrUtil.contains(upperCase, "PARTITIONED")) {
                partition = true;
            }
        }
    }

    @Override
    public String visitTableAllColumns(ImpalaParser.TableAllColumnsContext ctx) {
        String resultStr = super.visitTableAllColumns(ctx);
        // 校验SELECT语句中是否存在select * 语句
        resultStr = scanSelectAll(resultStr);
        return resultStr;
    }

    /**
     * @param node
     * @Method visitTerminal
     * @Param
     * @Return java.lang.String
     * @Description 重写基础节点，原基础节点返回值默认为null
     * @Author Wang zhihao
     * @Date 2024/2/1 19:23
     * @Version V1.0
     */
    @Override
    public String visitTerminal(TerminalNode node) {
        if (!node.getText().equals("<EOF>")) {
            String resultStr = node.getText();
            // 校验SELECT语句中是否存在DISTINCT关键字
            resultStr = scanDistinct(resultStr, node);
            return resultStr;
        }
        return super.visitTerminal(node);
    }

    /**
     * @param resultStr
     * @param ctx
     * @Method scanPartitionByMetadata
     * @Param
     * @Return void
     * @Description 根据元数据信息判断是否在insert时指定分区
     * @Author Wang zhihao
     * @Date 2023/11/3 18:54
     * @Version V1.0
     */
    private String scanPartitionByMetadata(String resultStr, ImpalaParser.InsertClauseContext ctx) {
        // 库表名全名
        String tableFullName = ctx.destination() != null
                ? ctx.destination().tableOrPartition().tableName().getText()
                : ctx.tableOrPartition().tableName().getText();
        //insert语句信息
        String insertInfo = ctx.getText();
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.SCAN_PART_BY_METADATA.getValue())) {
            String[] tableNameSplit = tableFullName.split("\\.");
            // 若有指定库名且为指定参数的，做参数替换
            String tableName = tableNameSplit.length == 1 ? tableNameSplit[0] : unFormatTableName(tableNameSplit[0]) + "." + tableNameSplit[1];
            boolean isNeedHavePart = timePartitionTableList.contains(tableName);
            boolean isHavePart = StrUtil.containsAnyIgnoreCase(insertInfo, "PARTITION");
            boolean isMark = isNeedHavePart != isHavePart;
            setScriptPackScanResults(AntlrEnum.SCAN_PART_BY_METADATA.getValue(), "insert", tableName, !isMark, "warning");
            return isMark ? getMarkResult(AntlrEnum.SCAN_PART_BY_METADATA.getDesc(), "warning") + resultStr : resultStr;
        }
        return resultStr;
    }

    /**
     * @param originalName
     * @Method unFormatTableName
     * @Param
     * @Return java.lang.String
     * @Description 获取配置文件信息做参数替换
     * @Author Wang zhihao
     * @Date 2023/12/5 20:08
     * @Version V1.0
     */
    private String unFormatTableName(String originalName) {
        try {
            return this.paramsConfig.get(originalName.toLowerCase());
        } catch (Exception e) {
            log.error("获取JSON配置信息异常");
            throw new RuntimeException("获取JSON配置信息异常");
        }
    }

    /**
     * @param functionInfo
     * @param resultStr
     * @Method sumFunctionScan
     * @Param
     * @Return java.lang.String
     * @Description 检查sum函数规则
     * @Author Wang zhihao
     * @Date 2024/2/2 14:25
     * @Version V1.0
     */
    private String sumFunctionScan(String functionInfo, String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.SUM_CHECK.getValue())) {
            boolean isMark = StrUtil.startWithIgnoreCase(functionInfo, "sum(") && !StrUtil.startWithAny(functionInfo.toLowerCase(), "sum(nvl(", "sum(coalesce(");
            if (isMark) {
                setScriptPackScanResults(AntlrEnum.SUM_CHECK.getValue(), "default", "tableName", false, "necessary");
            }
            return isMark ? resultStr + getMarkResult(AntlrEnum.SUM_CHECK.getDesc(), "necessary") : resultStr;
        }
        return resultStr;
    }

    /**
     * @param dbTableName
     * @param setColumnClause
     * @Method scanUpdate
     * @Param
     * @Return void
     * @Description 扫描update语句
     * @Author Wang zhihao
     * @Date 2023/11/13 10:48
     * @Version V1.0
     */
    private void scanUpdate(String dbTableName, String setColumnClause) {
        String columnClause = StrUtil.strip(setColumnClause, "set").trim();
        List<String> columnList = Arrays.stream(columnClause.split(",")).map(String::trim).collect(Collectors.toList());
        Map<String, String> columnMap = columnList.stream().map(s -> s.split("=")).collect(Collectors.toMap(s -> s[0], s -> s[1]));
        String tableName = dbTableName.contains(".") ? dbTableName.split("\\.")[1] : dbTableName;
        matchScan(tableName, columnMap);
    }

    /**
     * @param tableName
     * @Method scanCreate
     * @Param
     * @Return void
     * @Description 是否禁止出现CREATE检查
     * @Author Wang zhihao
     * @Date 2023/10/7 12:34
     * @Version V1.0
     */
    private String scanCreate(String tableName, String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.CREATE_CHECK.getValue())) {
            setScriptPackScanResults(AntlrEnum.CREATE_CHECK.getValue(), "CREATE", tableName, false, "necessary");
            return getMarkResult(AntlrEnum.CREATE_CHECK.getDesc(), "necessary") + "\n" + resultStr;
        }
        return resultStr;
    }

    /**
     * @param tableName
     * @Method scanCreate
     * @Param
     * @Return void
     * @Description 是否禁止出现ALTER检查
     * @Author Wang zhihao
     * @Date 2023/10/7 12:34
     * @Version V1.0
     */
    private String scanAlter(String tableName, String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.ALTER_CHECK.getValue()) && !fileName.endsWith("_alter.sql")) {
            setScriptPackScanResults(AntlrEnum.ALTER_CHECK.getValue(), "ALTER", tableName, false, "necessary");
            return getMarkResult(AntlrEnum.ALTER_CHECK.getDesc(), "necessary") + "\n" + resultStr;
        }
        return resultStr;
    }

    /**
     * @param tableName
     * @Method scanTableName
     * @Param
     * @Return void
     * @Description 检查建表名是否符合规范
     * @Author Wang zhihao
     * @Date 2023/10/7 12:36
     * @Version V1.0
     */
    private String scanTableName(String tableName, String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.CREATE_TABLE_NAME.getValue())) {
            boolean isMark = !checkNamesRegular(tableName);
            if (isMark) {
                setScriptPackScanResults(AntlrEnum.CREATE_TABLE_NAME.getValue(), "CREATE", tableName, false, "necessary");
            }
            return isMark ? getMarkResult(AntlrEnum.CREATE_TABLE_NAME.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    /**
     * @Method scanTableExist
     * @Param
     * @param tableName
     * @param sqlType
     * @Return void
     * @Description 检查表是否已在元数据库内
     * @Author Wang zhihao
     * @Date 2023/10/7 13:46
     * @Version V1.0
     */
  /*  private String scanTableExist(String tableName, String sqlType, String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.TABLE_EXISTS.getValue())) {
            LambdaQueryWrapper<ScanMetadata> wrapper = Wrappers.lambdaQuery();
            // 库名都是占位符
            String tbName = tableName.contains(".") ? tableName.split("\\.")[1] : tableName;
            wrapper.eq(ScanMetadata::getTblName, tbName);
            List<ScanMetadata> scanMetadata = hiveBaseSqlVisitor.scanMetadataService.wrapperScanMetadata(wrapper);
            boolean isMark = !scanMetadata.isEmpty();
            if (isMark) {
                setScriptPackScanResults(AntlrEnum.TABLE_EXISTS.getValue(), sqlType, tableName, false, "warning");
            }
            return isMark ? getMarkResult(AntlrEnum.TABLE_EXISTS.getDesc(), "warning") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }*/

    /**
     * @param tableName
     * @param sqlType
     * @Method scanDbName
     * @Param
     * @Return void
     * @Description 检查库名是否参数化
     * @Author Wang zhihao
     * @Date 2023/10/7 13:58
     * @Version V1.0
     */
    private String scanDbName(String tableName, String sqlType, String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.DB_NAME_CHECK.getValue())) {
            String dbName = tableName.split("\\.")[0];
            boolean isMark = !StrUtil.startWithIgnoreCase(dbName, "${") || !StrUtil.endWithIgnoreCase(dbName, "}");
            if (isMark) {
                setScriptPackScanResults(AntlrEnum.DB_NAME_CHECK.getValue(), sqlType, tableName, false, "necessary");
            }
            return isMark ? getMarkResult(AntlrEnum.DB_NAME_CHECK.getDesc(), "necessary") + "\n" + resultStr : resultStr;
        }
        return resultStr;
    }

    /**
     * @param resultStr
     * @param ctx
     * @Method scanColumnComment
     * @Param
     * @Return java.lang.String
     * @Description 注释存在性检查
     * @Author Wang zhihao
     * @Date 2024/2/4 17:31
     * @Version V1.0
     */
    private String scanColumnComment(String resultStr, ImpalaParser.ColumnNameTypeConstraintContext ctx) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.COLUMN_COMMENT.getValue())) {
            // 若不存在comment及具体注释则添加异常标记
            boolean isMark = ctx.KW_COMMENT() == null || ctx.StringLiteral() == null;
            if (isMark) {
                setScriptPackScanResults(AntlrEnum.COLUMN_COMMENT.getValue(), "CREATE", "default", false, "necessary");
            }
            return isMark ? resultStr + getMarkResult(AntlrEnum.COLUMN_COMMENT.getDesc(), "necessary") : resultStr;
        }
        return resultStr;
    }

    /**
     * @param resultStr
     * @param ctx
     * @Method scanColumnType
     * @Param
     * @Return java.lang.String
     * @Description 检验类型是否都为String与decimal两种
     * @Author Wang zhihao
     * @Date 2024/2/4 18:36
     * @Version V1.0
     */
    private String scanColumnType(String resultStr, ImpalaParser.ColumnNameTypeConstraintContext ctx) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.COLUMN_TYPE.getValue())) {
            // 若字段类型不为 string或decimal中的任一个则添加异常标记
            boolean isMark = ctx.colType() == null || !StrUtil.containsAnyIgnoreCase(ctx.colType().getText(), AntlrEnum.STRING.getValue(), AntlrEnum.DECIMAL.getValue());
            if (isMark) {
                setScriptPackScanResults(AntlrEnum.COLUMN_TYPE.getValue(), "CREATE", "default", false, "warning");
            }
            return isMark ? resultStr + getMarkResult(AntlrEnum.COLUMN_TYPE.getDesc(), "warning") : resultStr;
        }
        return resultStr;
    }

    /**
     * @param fieldColumnList
     * @param resultStr
     * @Method scanColumnNeed
     * @Param
     * @Return java.lang.String
     * @Description 扫描必须字段
     * @Author Wang zhihao
     * @Date 2024/2/4 18:19
     * @Version V1.0
     */
    private String scanColumnNeed(String fieldColumnList, String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.COLUMN_NEED.getValue())) {
            boolean insertTime = StrUtil.containsAnyIgnoreCase(fieldColumnList, "inserttime string");
            boolean batchNo = StrUtil.containsAnyIgnoreCase(fieldColumnList, "batchno decimal");
            setScriptPackScanResults(AntlrEnum.COLUMN_NEED.getValue(), "CREATE", "default", insertTime && batchNo, "warning");
            boolean isMark = !insertTime || !batchNo;
            return isMark ? resultStr + getMarkResult(AntlrEnum.COLUMN_NEED.getDesc(), "warning") : resultStr;
        }
        return resultStr;
    }

    /**
     * @Method scanColumnConsanguinity
     * @Param
     * @Return void
     * @Description 检查字段血缘
     * @Author Wang zhihao
     * @Date 2023/10/27 15:08
     * @Version V1.0
     */
    private String scanColumnConsanguinity(String resultStr, ImpalaParser.SelectItemContext ctx) {
        // 是否开启扫描，且检查该字段是否为库表字段
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.FILED_CONSANGUINITY.getValue())
                && AntlrUtil.queryDesignateChildren(ctx, ImpalaParser.TableOrColumnContext.class)) {
            boolean isMark = !StrUtil.startWith(ctx.getText(), "$") && !StrUtil.contains(ctx.getText(), '.');
            if (isMark) {
                setScriptPackScanResults(AntlrEnum.FILED_CONSANGUINITY.getValue(), "SELECT", "default", false, "warning");
            }
            return isMark ? resultStr + getMarkResult(AntlrEnum.FILED_CONSANGUINITY.getDesc(), "warning") : resultStr;
        }
        return resultStr;
    }

    /**
     * @param resultStr
     * @Method subqueryScan
     * @Param
     * @Return java.lang.String
     * @Description 子查询检查
     * @Author Wang zhihao
     * @Date 2024/2/5 17:06
     * @Version V1.0
     */
    private String subqueryScan(String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.SUB_QUERY_CHECK.getValue())) {
            setScriptPackScanResults(AntlrEnum.SUB_QUERY_CHECK.getValue(), "SELECT", "default", false, scriptPackScanResultMaps.get(AntlrEnum.SUB_QUERY_CHECK.getValue()).getScanLevel());
            return resultStr + getMarkResult(AntlrEnum.SUB_QUERY_CHECK.getDesc(), scriptPackScanResultMaps.get(AntlrEnum.SUB_QUERY_CHECK.getValue()).getScanLevel());
        }
        return resultStr;
    }

    /**
     * @param resultStr
     * @param ctx
     * @Method scanPartition
     * @Param
     * @Return java.lang.String
     * @Description 初始化建表语句中是否存在指定分区字段
     * @Author Wang zhihao
     * @Date 2024/2/5 10:58
     * @Version V1.0
     */
    private String scanPartition(String resultStr, ImpalaParser.TablePartitionContext ctx) {
        // 前提：父节点为建表语句，且分区字段开启检查
        if (AntlrUtil.queryDesignateParent(ctx, ImpalaParser.CreateTableStatementContext.class)
                && scriptPackScanResultMaps.containsKey(AntlrEnum.PARTITION_CHECK.getValue())) {
            // 获取分区字段信息
            String partitionInfo = AntlrUtil.assembleChildren(ctx);
            boolean isMark = !StrUtil.containsIgnoreCase(partitionInfo, AntlrEnum.SPECIFY_PARTITION_FIELD.getValue());
            if (isMark) {
                setScriptPackScanResults(AntlrEnum.PARTITION_CHECK.getValue(), "CREATE", "default", false, "warning");
            }
            return isMark ? resultStr + getMarkResult(AntlrEnum.PARTITION_CHECK.getDesc(), "warning") : resultStr;
        }
        return resultStr;
    }

    /**
     * @param scanName
     * @param sqlType
     * @param scanTable
     * @param scanState
     * @Method setScriptPackScanResults
     * @Param
     * @Return void
     * @Description 配置返回参数
     * @Author Wang zhihao
     * @Date 2023/10/7 10:43
     * @Version V1.0
     */
    private void setScriptPackScanResults(String scanName, String sqlType, String scanTable, boolean scanState, String level) {
        scriptPackScanResultMaps.get(scanName).setScanTable(scanTable);
        scriptPackScanResultMaps.get(scanName).setScanState(scanState ? "success" : "fail");
    }

    /**
     * @param name
     * @Method checkNamesRegular
     * @Param
     * @Return boolean
     * @Description 检查表名是否符合规范
     * @Author Wang zhihao
     * @Date 2023/10/7 11:02
     * @Version V1.0
     */
    private boolean checkNamesRegular(String name) {
        String tableName = name.contains(".") ? name.split("\\.")[1] : name;
        return tableName.matches(AntlrEnum.FILE_NAME_REGULAR.getValue());
    }

    /**
     * @param resultStr
     * @param node
     * @Method scanDistinct
     * @Param
     * @Return void
     * @Description 禁止使用distinct检查
     * @Author Wang zhihao
     * @Date 2023/10/7 16:25
     * @Version V1.0
     */
    private String scanDistinct(String resultStr, TerminalNode node) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.DISTINCT_CHECK.getValue())
                && StrUtil.equalsIgnoreCase(node.getText(), AntlrEnum.DISTINCT.getValue())
                && node.getParent().getClass() == ImpalaParser.SelectClauseContext.class) {
            setScriptPackScanResults(AntlrEnum.DISTINCT_CHECK.getValue(), "SELECT", "default", false, "warning");
            return resultStr + getMarkResult(AntlrEnum.DISTINCT_CHECK.getDesc(), "warning");
        }
        return resultStr;
    }

    /**
     * @param resultStr
     * @Method scanSelectAll
     * @Param
     * @Return void
     * @Description 不允许select *检查
     * @Author Wang zhihao
     * @Date 2023/10/7 16:27
     * @Version V1.0
     */
    private String scanSelectAll(String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.SELECT_ALL_CHECK.getValue())) {
            setScriptPackScanResults(AntlrEnum.SELECT_ALL_CHECK.getValue(), "SELECT", "default", false, "warning");
            return resultStr + getMarkResult(AntlrEnum.SELECT_ALL_CHECK.getDesc(), "warning");
        }
        return resultStr;
    }

    /**
     * @param resultStr
     * @param ctx
     * @Method scanWithAs
     * @Param
     * @Return void
     * @Description 禁用 with as 语句检查
     * @Author Wang zhihao
     * @Date 2023/10/7 16:35
     * @Version V1.0
     */
    private String withAsScan(String resultStr, ImpalaParser.WithClauseContext ctx) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.WITH_AS_CHECK.getValue())
                && AntlrUtil.queryDesignateParent(ctx, ImpalaParser.QueryStatementExpressionContext.class)) {
            String withAsInfo = AntlrUtil.assembleChildren(ctx);
            boolean isMark = StrUtil.startWithIgnoreCase(withAsInfo, "with") && StrUtil.containsAnyIgnoreCase(withAsInfo, "as(", "as (");
            if (isMark) {
                setScriptPackScanResults(AntlrEnum.WITH_AS_CHECK.getValue(), "WITH AS", "default", false, "warning");
            }
            return isMark ? getMarkResult(AntlrEnum.WITH_AS_CHECK.getDesc(), "warning") + resultStr : resultStr;
        }
        return resultStr;
    }

    /**
     * @param dbTableName
     * @param insertInfo
     * @param valueInfo
     * @Method scanInsert
     * @Param
     * @Return void
     * @Description 扫描检查insert-value语句
     * @Author Wang zhihao
     * @Date 2023/10/11 14:32
     * @Version V1.0
     */
    private void scanInsert(String dbTableName, String insertInfo, String valueInfo) {
        String tableName = dbTableName.contains(".") ? dbTableName.split("\\.")[1] : dbTableName;
        List<String> insertInfos = Arrays.stream(insertInfo.split(",")).map(String::trim).collect(Collectors.toList());
        List<String> valueInfos = Arrays.stream(StringUtils.strip(valueInfo, "VALUES()").split(",")).map(String::trim).collect(Collectors.toList());
        Map<String, String> insertValueMaps = IntStream.range(0, insertInfos.size())
                .collect(HashMap::new, (m, i) -> m.put(insertInfos.get(i), valueInfos.get(i)), (m, n) -> {
                });
        if (insertInfos.size() != valueInfos.size()) {
            throw new RuntimeException("扫描检查insert-value语句：字段数量与值的数量不对应");
        }
        // 根据表名匹配扫描检查
        matchScan(tableName, insertValueMaps);
    }

    /**
     * @param tableName
     * @Method matchScan
     * @Param
     * @Return void
     * @Description 根据表名匹配扫描检查
     * @Author Wang zhihao
     * @Date 2023/10/11 16:20
     * @Version V1.0
     */
    private void matchScan(String tableName, Map<String, String> insertValueMaps) {
        switch (tableName.toUpperCase()) {
            case "CTL_LOAD_CONFIG":
                scanCtlLoad(tableName, insertValueMaps);
                break;
            case "CTL_AUDIT_PARAM":
            case "CTL_AUDIT_PARAM_NEW":
                scanCtlAudit(tableName, insertValueMaps);
                break;
            case "CTL_PARAMS_CONFIG":
                scanCtlConfig(tableName, insertValueMaps);
                break;
            default:
                break;
        }
    }

    /**
     * @Method scanCtlLoad
     * @Param
     * @Return void
     * @Description 下载文件扫描
     * @Author Wang zhihao
     * @Date 2023/10/11 16:27
     * @Version V1.0
     */
    private void scanCtlLoad(String tableName, Map<String, String> insertValueMaps) {
        checkLoadAuditColumnParametric(tableName, insertValueMaps.get("POST_SQL_IMPALA"));
        checkLoadAuditColumnParametric(tableName, insertValueMaps.get("TRG_SCHEMA"));
    }

    /**
     * @Method scanCtlAudit
     * @Param
     * @Return void
     * @Description 稽核文件扫描
     * @Author Wang zhihao
     * @Date 2023/10/11 16:28
     * @Version V1.0
     */
    private void scanCtlAudit(String tableName, Map<String, String> insertValueMaps) {
        checkLoadAuditColumnParametric(tableName, insertValueMaps.get("SRC_SQL"));
        checkLoadAuditColumnParametric(tableName, insertValueMaps.get("TRG_SQL"));
    }

    /**
     * @Method scanCtlConfig
     * @Param
     * @Return void
     * @Description 推数文件扫描
     * @Author Wang zhihao
     * @Date 2023/10/11 16:28
     * @Version V1.0
     */
    private void scanCtlConfig(String tableName, Map<String, String> insertValueMaps) {
        checkPushColumnParametric(tableName, insertValueMaps.get("C_PUSHSQL"));
        checkPushColumnParametric(tableName, insertValueMaps.get("C_INCSQL"));
        checkPushColumnParametric(tableName, insertValueMaps.get("C_TASQL"));
    }

    /**
     * @Method checkColumnParametric
     * @Param
     * @Return void
     * @Description 检验下载与稽核文件中字段是否参数化
     * @Author Wang zhihao
     * @Date 2023/10/11 18:16
     * @Version V1.0
     */
    private void checkLoadAuditColumnParametric(String tableName, String columnValue) {
        if (StrUtil.isNotBlank(columnValue) && !StrUtil.containsAnyIgnoreCase(columnValue, "$${schema_stg}", "$${schema_ods}", "$${schema_dw}")) {
            setScriptPackScanResults(AntlrEnum.VALUE_DB_NAME.getValue(), "INSERT INTO", tableName, false, "necessary");
        }
    }

    /**
     * @param tableName
     * @param columnValue
     * @Method checkConfigColumnParametric
     * @Param
     * @Return void
     * @Description 检验推数文件中字段是否参数化
     * @Author Wang zhihao
     * @Date 2023/10/11 18:39
     * @Version V1.0
     */
    private void checkPushColumnParametric(String tableName, String columnValue) {
        if (StrUtil.isNotBlank(columnValue) && !StrUtil.containsAnyIgnoreCase(columnValue, "$${schema_stg}", "$${schema_ods}", "$${schema_dw}")) {
            setScriptPackScanResults(AntlrEnum.VALUE_DB_NAME.getValue(), "INSERT INTO", tableName, false, "necessary");
        }
    }

    /**
     * @param clauseInfo
     * @Method scanClauseSub
     * @Param
     * @Return void
     * @Description 扫描SQL语句中是否存在子查询
     * @Author Wang zhihao
     * @Date 2023/10/27 17:06
     * @Version V1.0
     */
    private void scanClauseSub(String clauseInfo) {
        if (StrUtil.containsAnyIgnoreCase(clauseInfo.toUpperCase(Locale.ROOT), "SELECT")) {
            setScriptPackScanResults(AntlrEnum.SUB_QUERY_CHECK.getValue(), "SELECT", "default", false, "warning");
        }
    }

    /**
     * @param scanName
     * @param scanLevel
     * @Method getMarkResult
     * @Param
     * @Return java.lang.String
     * @Description 获取SQL中的不同等级异常标记信息
     * @Author Wang zhihao
     * @Date 2024/2/6 13:34
     * @Version V1.0
     */
    //todo 优化或许做成一个实体对象来获取
    private String getMarkResult(String scanName, String scanLevel) {
        switch (scanLevel.toUpperCase(Locale.ROOT)) {
            case "WARNING":
                return StrUtil.format("{} {} {}", WARN_MARK_BEGIN, scanName, MARK_END);
            case "NECESSARY":
                return StrUtil.format("{} {} {}", ERROR_MARK_BEGIN, scanName, MARK_END);
            default:
                return "";
        }
    }

}
